class QueryDataByBase:

    # 查询不分店铺,无西北超票要求
    def query_notstore_notnw(id, tables):
        sql = """SELECT x.运单号, x.扫描时间, x.结算对象, x.结算对象编码,
                        x.产品类型, x.目的地省, x.目的地市, x.结算重量,
                        x.网点重量, x.`长*宽*高`, x.签收网点, x.运单发放,
                        x.运单发放网点, x.扫描员, x.扫描员工号, x.有偿派费, x.备注,
                        (CASE x.产品类型
                           WHEN "普通件" THEN
                               (CASE y.carry
                                       WHEN 0 THEN
                                           y.head_price +
                                           (x.结算重量 - y.head_kg) * 
                                           (y.cte_kg / y.cte_unit) + 
                                           y.cte_fixed -
                                           x.unit_price
                                       WHEN 1 THEN
                                           y.head_price + 
                                           (CEIL(x.结算重量 / y.carry) * 
                                           y.carry - y.head_kg) * 
                                           (y.cte_kg / y.cte_unit) + 
                                           y.cte_fixed -
                                           x.unit_price
                                       WHEN 0.1 THEN
                                           y.head_price + 
                                           (CEIL(x.结算重量 / y.carry) * 
                                           y.carry - y.head_kg) * 
                                           (y.cte_kg / y.cte_unit) + 
                                           y.cte_fixed -
                                           x.unit_price
                                       WHEN 0.5 THEN
                                           y.head_price + 
                                           (CEIL(x.结算重量 / y.carry) * 
                                           y.carry - y.head_kg) * 
                                           (y.cte_kg / y.cte_unit) + 
                                           y.cte_fixed -
                                           x.unit_price
                                       WHEN 2 THEN
                                           y.head_price + 
                                           (CEIL(x.结算重量 / y.carry) * 
                                           y.carry - y.head_kg) * 
                                           (y.cte_kg / y.cte_unit) + 
                                           y.cte_fixed -
                                           x.unit_price
                               END )
                           WHEN "时效件" THEN
                               x.em_price - x.unit_price
                        END) "中转费"
                        FROM (SELECT * 
                                    FROM mydata.{tables} m
                                    LEFT JOIN customer_quotation.empty_unit e
                                    ON m.结算对象编码 = e.objemun_id
                                    WHERE m.结算对象编码 = "75512.{id}"
                                    AND m.备注 NOT IN("回单", "错扫", "非本站",
                                                     "未发出", "集团客户", "错扫收件",
                                                     "非本站面单", "国际件")) x
                        LEFT JOIN customer_quotation.obj_{id} y
                        ON x.结算对象编码 = y.obj_id
                        WHERE x.目的地省 = y.states
                        AND (CASE IF(y.store = "empty", 1, 0)
                                WHEN 0 THEN
                                    x.运单发放 = y.store
                                ELSE
                                    x.运单发放 = x.运单发放
                            END)
                        AND x.结算重量 > y.stage_min
                        AND x.结算重量 <= y.stage_max
                        AND (x.结算重量 > 0 OR x.网点重量 > 0)
                        ORDER BY x.运单发放;""".format(id=id.strip().replace(' ', ''), tables=tables)
        return sql

    # 查询分店铺,无西北超票要求
    def query_isstores_notnw(id, tables, name):
        sql = """SELECT x.运单号, x.扫描时间, x.结算对象, x.结算对象编码,
                         x.产品类型, x.目的地省, x.目的地市, x.结算重量, x.网点重量,
                         x.`长*宽*高`, x.签收网点, x.运单发放, x.运单发放网点, x.扫描员,
                         x.扫描员工号, x.有偿派费, x.备注,
                         (CASE x.产品类型
                            WHEN "普通件" THEN
                                (CASE y.carry
                                        WHEN 0 THEN
                                            y.head_price +
                                            (x.结算重量 - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed -
                                            x.unit_price
                                        WHEN 1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed -
                                            x.unit_price
                                        WHEN 0.1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed -
                                            x.unit_price
                                        WHEN 0.5 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed -
                                            x.unit_price
                                        WHEN 2 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed -
                                            x.unit_price
                                END )
                            WHEN "时效件" THEN
                                x.em_price - x.unit_price
                         END) "中转费"
                         FROM (SELECT * 
                                     FROM mydata.{tables} m
                                     LEFT JOIN customer_quotation.empty_unit e
                                     ON m.结算对象编码 = e.objemun_id
                                     WHERE m.结算对象编码 = "75512.{id}"
                                     AND m.运单发放 = e.emun_store
                                     AND m.运单发放 = "{name}"
                                     AND m.备注 NOT IN("回单", "错扫",
                                                      "非本站", "未发出",
                                                      "集团客户", "错扫收件",
                                                      "非本站面单", "国际件")) x
                         LEFT JOIN customer_quotation.obj_{id} y
                         ON x.结算对象编码 = y.obj_id
                         WHERE x.目的地省 = y.states
                         AND (CASE IF(y.store = "empty", 1, 0)
                                 WHEN 0 THEN
                                     x.运单发放 = y.store
                                 ELSE
                                     x.运单发放 = x.运单发放
                             END)
                         AND x.结算重量 > y.stage_min
                         AND x.结算重量 <= y.stage_max
                         AND (x.结算重量 > 0 OR x.网点重量 > 0)
                         ORDER BY x.运单发放;""".format(id=id.strip().replace(' ', ''), tables=tables, name=name)
        return sql

    # 查询不分店铺,存在西北超票要求
    def query_notstore_isnw(id, tables, num):
        sql = """SELECT a.运单号, a.扫描时间, a.结算对象, a.结算对象编码,
                         a.产品类型, a.目的地省, a.目的地市, a.结算重量, a.网点重量,
                         a.`长*宽*高`, a.签收网点, a.运单发放, a.运单发放网点, a.扫描员,
                         a.扫描员工号, a.有偿派费, a.备注, a.pricea "中转费", b.priceb "西北六省发件"
                 FROM (SELECT *,
                              (CASE x.产品类型
                                 WHEN "普通件" THEN
                                     (CASE y.carry
                                         WHEN 0 THEN
                                             y.head_price +
                                             (x.结算重量 - y.head_kg) * 
                                             (y.cte_kg / y.cte_unit) + 
                                             y.cte_fixed -
                                             x.unit_price
                                         WHEN 1 THEN
                                             y.head_price + 
                                             (CEIL(x.结算重量 / y.carry) * 
                                             y.carry - y.head_kg) * 
                                             (y.cte_kg / y.cte_unit) + 
                                             y.cte_fixed -
                                             x.unit_price
                                         WHEN 0.1 THEN
                                             y.head_price + 
                                             (CEIL(x.结算重量 / y.carry) * 
                                             y.carry - y.head_kg) * 
                                             (y.cte_kg / y.cte_unit) + 
                                             y.cte_fixed -
                                             x.unit_price
                                         WHEN 0.5 THEN
                                             y.head_price + 
                                             (CEIL(x.结算重量 / y.carry) * 
                                             y.carry - y.head_kg) * 
                                             (y.cte_kg / y.cte_unit) + 
                                             y.cte_fixed -
                                             x.unit_price
                                         WHEN 2 THEN
                                             y.head_price + 
                                             (CEIL(x.结算重量 / y.carry) * 
                                             y.carry - y.head_kg) * 
                                             (y.cte_kg / y.cte_unit) + 
                                             y.cte_fixed -
                                             x.unit_price
                                     END)
                                 WHEN "时效件" THEN
                                     x.em_price - x.unit_price
                              END) "pricea"
                       FROM (SELECT * 
                                    FROM mydata.{tables} m
                                    LEFT JOIN customer_quotation.empty_unit e
                                    ON m.结算对象编码 = e.objemun_id
                                    WHERE m.结算对象编码 = "75512.{id}"
                                    AND m.备注 NOT IN("回单", "错扫",
                                                      "非本站", "未发出",
                                                      "集团客户", "错扫收件",
                                                      "非本站面单", "国际件")) x
                             LEFT JOIN customer_quotation.obj_{id} y
                             ON x.结算对象编码 = y.obj_id
                             WHERE x.目的地省 = y.states
                             AND (CASE IF(y.store = "empty", 1, 0)
                                     WHEN 0 THEN
                                         x.运单发放 = y.store
                                     ELSE
                                         x.运单发放 = x.运单发放
                                 END)
                             AND x.结算重量 > y.stage_min
                             AND x.结算重量 <= y.stage_max
                             AND (x.结算重量 > 0 OR x.网点重量 > 0)) a
                 LEFT JOIN (SELECT x.运单号 way_id,
                                   (CASE y.carry
                                        WHEN 0 THEN
                                            y.head_price +
                                            (x.结算重量 - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 0.1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 0.5 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 2 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                   END ) "priceb"
                           FROM (SELECT * 
                                 FROM (SELECT *
                                       FROM mydata.{tables} m
                                       WHERE m.结算对象编码 = "75512.{id}"
                                       AND m.目的地省
                                       IN("内蒙古自治区", "甘肃省",
                                          "宁夏回族自治区", "青海省",
                                          "新疆维吾尔自治区", "西藏自治区")
                                       AND m.备注 NOT IN("回单", "错扫", "非本站",
                                                         "未发出", "集团客户", "错扫收件",
                                                         "非本站面单", "国际件")) a
                                 LEFT JOIN  (SELECT m.运单号 way_id
                                             FROM mydata.{tables} m
                                             WHERE m.结算对象编码 = "75512.{id}"
                                             AND m.目的地省 IN("内蒙古自治区", "甘肃省",
                                                              "宁夏回族自治区", "青海省",
                                                              "新疆维吾尔自治区", "西藏自治区")
                                             AND m.备注 NOT IN("回单", "错扫", "非本站",
                                                              "未发出", "集团客户", "错扫收件",
                                                              "非本站面单", "国际件")
                                             AND (m.结算重量 > 0 OR m.网点重量 > 0)
                                             ORDER BY m.扫描时间
                                             DESC LIMIT {num}) b
                                 ON a.运单号 = b.way_id
                                 WHERE b.way_id IS NOT NULL) x
                           LEFT JOIN customer_quotation_nw.nwobj_{id} y
                           ON x.结算对象编码 = y.objw_id
                           WHERE x.结算重量 > y.stage_min
                           AND x.结算重量 <= y.stage_max
                           ) b
                 ON a.运单号 = b.way_id
                 ORDER BY a.运单发放;""".format(id=id.strip().replace(' ', ''), tables=tables, num=num)
        return sql

    # 查询分店铺,存在西北超票要求
    def query_isstores_isnw(id, tables, num, name):
        sql = """SELECT a.运单号, a.扫描时间, a.结算对象, a.结算对象编码, a.产品类型,
                        a.目的地省, a.目的地市, a.结算重量, a.网点重量, a.`长*宽*高`,
                        a.签收网点, a.运单发放, a.运单发放网点, a.扫描员, a.扫描员工号,
                        a.有偿派费, a.备注, a.pricea "中转费", b.priceb "西北六省发件"
                 FROM (SELECT *,
                              (CASE x.产品类型
                                  WHEN "普通件" THEN
                                      (CASE y.carry
                                          WHEN 0 THEN
                                              y.head_price +
                                              (x.结算重量 - y.head_kg) * 
                                              (y.cte_kg / y.cte_unit) +
                                              y.cte_fixed -
                                              x.unit_price
                                          WHEN 1 THEN
                                              y.head_price + 
                                              (CEIL(x.结算重量 / y.carry) * 
                                              y.carry - y.head_kg) * 
                                              (y.cte_kg / y.cte_unit) + 
                                              y.cte_fixed -
                                              x.unit_price
                                          WHEN 0.1 THEN
                                              y.head_price + 
                                              (CEIL(x.结算重量 / y.carry) * 
                                              y.carry - y.head_kg) * 
                                              (y.cte_kg / y.cte_unit) + 
                                              y.cte_fixed -
                                              x.unit_price
                                          WHEN 0.5 THEN
                                              y.head_price + 
                                              (CEIL(x.结算重量 / y.carry) * 
                                              y.carry - y.head_kg) * 
                                              (y.cte_kg / y.cte_unit) + 
                                              y.cte_fixed -
                                              x.unit_price
                                          WHEN 2 THEN
                                              y.head_price + 
                                              (CEIL(x.结算重量 / y.carry) * 
                                              y.carry - y.head_kg) * 
                                              (y.cte_kg / y.cte_unit) + 
                                              y.cte_fixed -
                                              x.unit_price
                                      END )
                                  WHEN "时效件" THEN
                                      x.em_price - x.unit_price
                              END) "pricea"
                       FROM (SELECT * 
                             FROM mydata.{tables} m
                             LEFT JOIN customer_quotation.empty_unit e
                             ON m.结算对象编码 = e.objemun_id
                             WHERE m.结算对象编码 = "75512.{id}"
                             AND m.运单发放 = e.emun_store
                             AND m.运单发放 = "{name}"
                             AND m.备注 NOT IN("回单", "错扫", "非本站",
                                               "未发出", "集团客户", "错扫收件",
                                               "非本站面单", "国际件")
                             AND m.备注 NOT IN("回单", "错扫", "非本站", "未发出",
                                              "集团客户", "错扫收件", "非本站面单", "国际件")) x
                       LEFT JOIN customer_quotation.obj_{id} y
                       ON x.结算对象编码 = y.obj_id
                       WHERE x.目的地省 = y.states
                       AND (CASE IF(y.store = "empty", 1, 0)
                               WHEN 0 THEN
                                   x.运单发放 = y.store
                               ELSE
                                   x.运单发放 = x.运单发放
                           END)
                       AND x.结算重量 > y.stage_min
                       AND x.结算重量 <= y.stage_max
                       AND (x.结算重量 > 0 OR x.网点重量 > 0)) a
                 LEFT JOIN (SELECT x.运单号 way_id,
                                   (CASE y.carry
                                        WHEN 0 THEN
                                            y.head_price +
                                            (x.结算重量 - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 0.1 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 0.5 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                        WHEN 2 THEN
                                            y.head_price + 
                                            (CEIL(x.结算重量 / y.carry) * 
                                            y.carry - y.head_kg) * 
                                            (y.cte_kg / y.cte_unit) + 
                                            y.cte_fixed 
                                   END ) "priceb"
                           FROM (SELECT * 
                                 FROM (SELECT *
                                       FROM mydata.{tables} m
                                       WHERE m.结算对象编码 = "75512.{id}"
                                       AND m.目的地省
                                       IN("内蒙古自治区", "甘肃省",
                                          "宁夏回族自治区", "青海省",
                                          "新疆维吾尔自治区", "西藏自治区")
                                       AND m.备注 NOT IN("回单", "错扫", "非本站",
                                                         "未发出", "集团客户", "错扫收件",
                                                         "非本站面单", "国际件")) a
                                 LEFT JOIN  (SELECT m.运单号 way_id
                                             FROM mydata.{tables} m
                                             WHERE m.结算对象编码 = "75512.{id}"
                                             AND m.运单发放 = "{name}"
                                             AND m.目的地省 IN("内蒙古自治区", "甘肃省", "宁夏回族自治区",
                                                             "青海省", "新疆维吾尔自治区", "西藏自治区")
                                             AND m.备注 NOT IN("回单", "错扫", "非本站",
                                                              "未发出", "集团客户", "错扫收件",
                                                              "非本站面单", "国际件")
                                             AND (m.结算重量 > 0 OR m.网点重量 > 0)
                                             ORDER BY m.扫描时间
                                             DESC LIMIT {num}) b
                                 ON a.运单号 = b.way_id
                                 WHERE b.way_id IS NOT NULL) x
                           LEFT JOIN customer_quotation_nw.nwobj_{id} y
                           ON x.结算对象编码 = y.objw_id
                           WHERE x.结算重量 > y.stage_min
                           AND x.结算重量 <= y.stage_max
                           AND x.运单发放 = y.store) b
                 ON a.运单号 = b.way_id
                 ORDER BY a.运单发放;""".format(id=id.strip().replace(' ', ''), tables=tables, num=num, name=name)
        return sql

    # 查询不分店铺情况下的西北超标票数
    def query_nwnum_quotation(id, tables):
         sql = """SELECT
                  CEIL(
                  IF(
                  ((SELECT
                  (SELECT COUNT(*)
                  FROM mydata.{tables} m
                  WHERE m.结算对象编码 = "75512.{id}"
                  AND m.目的地省 IN("内蒙古自治区", "甘肃省",
                                   "宁夏回族自治区", "青海省",
                                   "新疆维吾尔自治区", "西藏自治区")
                  AND m.备注 NOT IN("回单", "错扫",
                                    "非本站", "未发出",
                                    "集团客户", "错扫收件",
                                    "非本站面单", "国际件"))
                  /(SELECT COUNT(*)
                  FROM mydata.{tables} m
                  WHERE m.结算对象编码 = "75512.{id}"
                  AND m.备注 NOT IN("回单", "错扫",
                                   "非本站", "未发出",
                                   "集团客户", "错扫收件",
                                   "非本站面单", "国际件")))
                  -(SELECT c.proportion
                  FROM customer_quotation_nw.nwobj_{id} c
                  LIMIT 1))
                  > 0,((SELECT
                  (SELECT COUNT(*)
                  FROM mydata.{tables} m
                  WHERE m.结算对象编码 = "75512.{id}"
                  AND m.目的地省 IN("内蒙古自治区", "甘肃省",
                                   "宁夏回族自治区", "青海省",
                                   "新疆维吾尔自治区", "西藏自治区")
                  AND m.备注 NOT IN("回单", "错扫",
                                    "非本站", "未发出",
                                    "集团客户", "错扫收件",
                                    "非本站面单", "国际件"))
                  /(SELECT COUNT(*)
                    FROM mydata.{tables} m
                    WHERE m.结算对象编码 = "75512.{id}"
                    AND m.备注 NOT IN("回单", "错扫",
                                     "非本站", "未发出",
                                     "集团客户", "错扫收件",
                                     "非本站面单", "国际件")))
                  -(SELECT c.proportion
                    FROM customer_quotation_nw.nwobj_{id} c
                    LIMIT 1)),0) * (SELECT COUNT(*)
                                    FROM mydata.{tables} m
                                    WHERE m.结算对象编码 = "75512.{id}"
                                    AND m.备注 NOT IN("回单", "错扫",
                                                     "非本站", "未发出",
                                                     "集团客户", "错扫收件",
                                                     "非本站面单", "国际件"))) "polls";""".format(id=id.strip().replace(' ', ''), tables=tables)
         return sql

    # 查询分店铺情况下各个店铺的西北超出票数
    def diff_query_nwsum_quotation(id, tables):
        sql = """SELECT aw.运单发放 store,IF((CASE ISNULL(CEIL(aw.ex_poll * bw.poll ))
                                         WHEN 1 THEN
                                            0
                                         WHEN 0 THEN
                                            CEIL(aw.ex_poll * bw.poll )
                                         END) > 0,
                                             (CASE ISNULL(CEIL(aw.ex_poll * bw.poll ))
                                         WHEN 1 THEN
                                            0
                                         WHEN 0 THEN
                                            CEIL(aw.ex_poll * bw.poll )
                                         END) , 0) ex_polls
                 FROM (SELECT ax.运单发放,ax.w_poll - bx.proportion ex_poll
                       FROM (SELECT a.运单发放,b.poll / a.poll w_poll
                             FROM (SELECT m.运单发放,COUNT(*) poll
                                   FROM mydata.{tables} m
                                   WHERE m.结算对象编码 = "75512.{id}"
                                   AND m.运单发放 IN((SELECT c.store
                                                    FROM customer_quotation_nw.nwobj_{id} c))
                                   GROUP BY m.运单发放) a
                             LEFT JOIN (SELECT m.运单发放,COUNT(*) poll
                                        FROM mydata.{tables} m
                                        WHERE m.`结算对象编码` = "75512.{id}"
                                        AND m.目的地省 IN("内蒙古自治区", "甘肃省",
                                                         "宁夏回族自治区", "青海省",
                                                         "新疆维吾尔自治区", "西藏自治区")
                                        AND m.备注 NOT IN("回单", "错扫",
                                                         "非本站", "未发出",
                                                         "集团客户", "错扫收件",
                                                         "非本站面单", "国际件")
                                        GROUP BY m.运单发放) b
                             ON a.运单发放 = b.运单发放) ax
                       LEFT JOIN (SELECT *
                                  FROM customer_quotation_nw.nwobj_{id} c
                                  GROUP BY c.store) bx
                       ON ax.运单发放 = bx.store) aw
                 LEFT JOIN (SELECT m.运单发放,COUNT(*) poll
                            FROM mydata.{tables} m
                            WHERE m.`结算对象编码` = "75512.{id}"
                            AND m.运单发放 IN((SELECT c.store
                                             FROM customer_quotation_nw.nwobj_{id} c))
                            GROUP BY m.运单发放) bw
                 ON aw.运单发放 = bw.运单发放;""".format(id=id.strip().replace(' ', ''), tables=tables)
        return sql




    def simulate_operation(id, states, kg):
        sql = """SELECT y.head_price "head_price",
                        x.unit_price "unit_price",
                        FORMAT((CASE y.carry
                            WHEN 0 THEN
                                y.head_price +
                                ({kg} - y.head_kg) * 
                                (y.cte_kg / y.cte_unit) +
                                y.cte_fixed -
                                x.unit_price
                            WHEN 1 THEN
                                y.head_price + 
                                (CEIL({kg} / y.carry) * 
                                y.carry - y.head_kg) * 
                                (y.cte_kg / y.cte_unit) + 
                                y.cte_fixed -
                                x.unit_price
                            WHEN 0.1 THEN
                                y.head_price + 
                                (CEIL({kg} / y.carry) * 
                                y.carry - y.head_kg) * 
                                (y.cte_kg / y.cte_unit) + 
                                y.cte_fixed -
                                x.unit_price
                            WHEN 0.5 THEN
                                y.head_price + 
                                (CEIL({kg} / y.carry) * 
                                y.carry - y.head_kg) * 
                                (y.cte_kg / y.cte_unit) + 
                                y.cte_fixed -
                                x.unit_price
                            WHEN 2 THEN
                                y.head_price + 
                                (CEIL({kg} / y.carry) * 
                                y.carry - y.head_kg) * 
                                (y.cte_kg / y.cte_unit) + 
                                y.cte_fixed -
                                x.unit_price
                        END), 2) "cte_price"
                        FROM customer_quotation.obj_{id} y
                        LEFT JOIN customer_quotation.empty_unit x
                        ON y.obj_id = x.objemun_id
                        WHERE y.states = "{states}"
                        AND y.stage_min < {kg}
                        AND y.stage_max >= {kg};""".format(id=id.strip().replace(' ', ''),
                                                           states=states,
                                                           kg=kg.strip().replace(' ', ''))
        return sql

    # 获取指定客户报价
    def get_pricebyobjid(id):
        sql = f"""SELECT *
                  FROM customer_quotation.obj_{id};"""

        return sql

    # 获取指定客户西北超标报价
    def get_nw_pricebyobjid(id):
        sql = f"""SELECT *
                  FROM customer_quotation_nw.nwobj_{id};"""

        return sql

    # 根据客户工号删除报价表
    def del_pricetablebyobjid(id):
        sql = f"""DROP TABLE customer_quotation.obj_{id};"""

        return sql

    # 根据客户工号删除买单价表
    def del_pricedatabyobjid(id):
        sql = f"""DELETE
                  FROM customer_quotation.empty_unit c
                  WHERE c.objemun_id = "75512.{id}";"""
        return sql

    # 根据客户工号查询买单价表
    def get_pricedatabyobjid(id):
        sql = f"""SELECT c.emun_id, c.em_price, c.unit_price, c.emun_store, c.entering_clerk
                  FROM customer_quotation.empty_unit c
                  WHERE c.objemun_id = "75512.{id}";"""
        return sql

    # 根据客户工号删除西北报价表
    def del_pricenwtablebyobjid(id):
        sql = f"""DROP TABLE customer_quotation_nw.nwobj_{id};"""
        return sql


    # 查询不考虑西北情况下分店铺的各个店铺名称
    def query_diffstore_name(id):
        sql = f"""SELECT c.store
                  FROM customer_quotation.obj_{id} c
                  GROUP BY c.store""".format(id=id.strip().replace(' ', ''))
        return sql

    # 判断存在西北超票情况下是否分店铺
    def isornot_diffstore(id):
        sql = f"""SELECT IF(c.store = "empty", 1, 0)
                  FROM customer_quotation_nw.nwobj_{id} c
                  LIMIT 1;""".format(id=id.strip().replace(' ', ''))
        return sql

    # 判断不存在西北超票情况下是否分店铺
    def isornot_notnw_diffstore(id):
        sql = f"""SELECT IF(c.store = "empty", 1, 0)
                  FROM customer_quotation.obj_{id} c
                  LIMIT 1;""".format(id=id.strip().replace(' ', ''))
        return sql

    # 判断是否存在西北超标报价表
    def query_havetable_quotation(id):
        sql = """SELECT ISNULL((SELECT table_name
                  FROM information_schema.TABLES
                  WHERE table_name = "nwobj_{id}"));""".format(id=id.strip().replace(' ', ''))
        return sql

    # 通过用户名查询用户基本信息
    #   参数:
    #   username:用户名
    def verify_the_user(username):
        sql = """SELECT u.password,u.permission_type,u.real_name
                  FROM users.accounting_department u
                  WHERE u.user_name = "{username}";""".format(username=username.strip().replace(' ', ''))
        return sql

